package com.xm.dao.background;

import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 商品库存
 * @author 康文程
 * @date 2024-1-12 上午08:53
 */
public class CommodityWarehouseDao extends BaseUtil {
    /**
     * 更新商品库存
     *
     * @param commodityWarehouseId 商品货仓编号
     * @param newInventory         更新后的库存数量
     * @return 更新是否成功
     * @throws SQLException SQL执行异常
     */
    public boolean updateInventory(int commodityWarehouseId, int newInventory) throws SQLException {
        @Language("sql")
        String sql = "UPDATE commodity_warehouse SET details_inventory = ? WHERE commodity_warehouse_id = ?";
        Object[] params = {newInventory, commodityWarehouseId};
        int rowsAffected = executeUpdate(sql, params);
        return rowsAffected > 0;
    }
    /**
     * 查询库存商品数量
     * @return 订单数量
     * @throws SqlErrorException SQL执行异常
     */
    public int getCommodityWarehouseNine() throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT sum(details_inventory) FROM commodity_warehouse";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql);
            if (resultSet.next()) {
                return resultSet.getInt(1);
            } else {
                return 0;
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }
    /**
     * 查询库存商品数量小于等于50的记录，并插入到事项表中
     * @throws SqlErrorException SQL执行异常
     */
    public void insertMattersFromCommodityWarehouse() throws SqlErrorException {
        @Language("SQL")
        String selectSql = "SELECT w.warehouse_name, c.commodity_name, cw.commodity_id " +
                "FROM commodity_warehouse cw " +
                "LEFT JOIN commodity c ON c.commodity_id = cw.commodity_id " +
                "LEFT JOIN warehouse w ON cw.warehouse_id = w.warehouse_id " +
                "WHERE cw.details_inventory <= 50";

        ResultSet resultSet = null;

        try {
            resultSet = executeQuery(selectSql);

            while (resultSet.next()) {
                String warehouseName = resultSet.getString(1);
                String commodityName = resultSet.getString(2);
                int commodityId = resultSet.getInt(3);

                @Language("SQL")
                String checkExistSql = "SELECT COUNT(*) FROM matters WHERE matters_title = ? AND commodity_id = ?";
                Object[] checkParams = { warehouseName + "的" + commodityName + "库存数量低于50", commodityId };
                ResultSet countResultSet = executeQuery(checkExistSql, checkParams);
                if (countResultSet.next() && countResultSet.getInt(1) > 0) {
                    // 如果已存在相同数据，则跳过插入操作
                    continue;
                }
                @Language("SQL")
                String insertSql = "INSERT INTO matters (matters_title, matters_type, matters_status, matters_date, commodity_id) " +
                        "VALUES (?, 1, 0, NOW(), ?)";

                Object[] params = { warehouseName + "的" + commodityName + "库存数量低于50", commodityId };

                int rowsAffected = executeUpdate(insertSql, params);
                if (rowsAffected <= 0) {
                    System.out.println("插入数据失败");
                }
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询并插入失败", e);
        } finally {
            close(resultSet);
        }
    }
    /**
     * 新增商品库存
     *
     * @param warehouseId   仓库Id
     * @param commodityId   商品Id
     * @return 新增是否成功
     * @throws SqlErrorException SQL执行异常
     */
    public boolean addCommodityWarehouse(Integer warehouseId, Integer commodityId) throws SqlErrorException {
        @Language("SQL")
        String insertSql = "INSERT INTO commodity_warehouse (details_inventory, purchase_limit, warehouse_id,commodity_id) " +
                "VALUES (500,2,?,?)";

        Object[] params = {warehouseId, commodityId};

        int rowsAffected = executeUpdate(insertSql, params);
        return rowsAffected > 0;
    }
}
